import datetime
from typing import Dict

from conf.config import MYSQL2_CONFIG
from db.mysql_db import Mysql2Client


class HousePriceDAO(object):
    """ 房价访问对象 """

    def __init__(self, *args, **kwargs):
        super(HousePriceDAO, self).__init__(*args, **kwargs)

    @staticmethod
    def get_mysql_conn():
        """ 获取mysql连接 """
        mysql_conn = Mysql2Client(MYSQL2_CONFIG)
        return mysql_conn

    def get_city_house_price(self, year: int, month: int, city_name: str, house_type: str) -> int:
        """
        查询城市房价
        :param year: 年份
        :param month: 月份
        :param city_name: 城市名称
        :param house_type: 房屋类型
        :return: 城市房价
        """
        mysql_conn = self.get_mysql_conn()
        city_house_price = None
        try:
            sql = ('SELECT house_price , `year`, `month`, city_name, '
                   'district_name, house_type '
                   'FROM house_price '
                   'WHERE `year` = %s AND `month` = %s '
                   'AND city_name = %s AND district_name = %s '
                   'AND house_type = %s;')
            args = (year, month,
                    city_name, city_name,
                    house_type)
            city_house_price = mysql_conn.fetchone(sql, args=args)
            if city_house_price:
                city_house_price = city_house_price.get("house_price")
            else:
                city_house_price = None
        finally:
            if "mysql_conn" in dir():  # 判断连接是否成功创建，创建了才能执行close()
                mysql_conn.close()

        return city_house_price

    def get_district_house_price(self, year: int, month: int, city_name: str, house_type: str) -> Dict:
        """
        查询特定城市各区域的房价
        :param year: 年份
        :param month: 月份
        :param city_name: 城市名称
        :param house_type: 房屋类型
        :return: 特定城市各区域的房价
        """
        mysql_conn = self.get_mysql_conn()
        district_house_price = dict()
        try:
            sql = ('SELECT house_price , `year`, `month`, city_name, '
                   'district_name, house_type '
                   'FROM house_price '
                   'WHERE `year` = %s AND `month` = %s '
                   'AND city_name = %s AND district_name != %s '
                   'AND district_name NOT LIKE "其他%%" '
                   'AND district_name NOT LIKE "周边城市%%" '
                   'AND house_type = %s '
                   'ORDER BY district_name')
            args = (year, month,
                    city_name, city_name,
                    house_type)
            count, district_house_price = mysql_conn.fetchall(sql, args=args)
        finally:
            if "mysql_conn" in dir():  # 判断连接是否成功创建，创建了才能执行close()
                mysql_conn.close()

        return district_house_price

    def get_past_year_new_house_price(self, city_name: str, house_type: str) -> int:
        """
        查询过去一年的城市房价
        :param city_name: 城市名称
        :param house_type: 房屋类型
        :return: 城市房价
        """
        mysql_conn = self.get_mysql_conn()

        today = datetime.date.today()
        # 获取这个月的一号
        first_day_of_this_month = datetime.date(today.year, today.month, 1).strftime("%Y-%m-%d")
        # 获取去年的这个月一号
        first_day_of_last_year_this_month = datetime.date(today.year - 1, today.month, 1).strftime("%Y-%m-%d")

        past_year_new_house_price = dict()
        try:
            sql = ('SELECT house_price, `year`, `month` FROM house_price '
                   'WHERE city_name = %s AND district_name = %s AND house_type = %s '
                   'AND STR_TO_DATE(CONCAT(`year`, "-", `month`, "-01"), "%%Y-%%m-%%d")  >= %s '
                   'AND STR_TO_DATE(CONCAT(`year`, "-", `month`, "-01"), "%%Y-%%m-%%d") < %s '
                   'ORDER BY `year`, `month`, district_name;')
            args = (city_name, city_name, house_type,
                    first_day_of_last_year_this_month,
                    first_day_of_this_month)
            count, past_year_new_house_price = mysql_conn.fetchall(sql, args=args)
        finally:
            if "mysql_conn" in dir():  # 判断连接是否成功创建，创建了才能执行close()
                mysql_conn.close()

        return past_year_new_house_price

    def get_city_house_price2(self, start_date: str, end_date: str, district_id: int, house_type: int):
        """
        查询某段时间内的房价
        :param start_date: 开始日期（闭区间，含该时间点）
        :param end_date: 截止日期（闭区间，含该时间点）
        :param district_id: 区域id（城市id）
        :param house_type: 房屋类型
        :return: 城市房价列表
        """
        mysql_conn = self.get_mysql_conn()
        city_house_price = dict()
        try:
            sql = ('SELECT c1.bpid, c1.BID, c1.htype AS house_type, c1.AveragePrice AS house_price, '
                   'c1.CTime AS ctime, d.`Name` AS city_name '
                   'FROM (SELECT MAX(c.bpid) AS bpid, BID, c.htype, CTime FROM (SELECT '
                   'htype, bpid, BID, CTime FROM cityprice '
                   'WHERE CTime >= %s AND CTime <= %s '
                   'AND bid = %s AND htype = %s) c GROUP BY CTime, c.htype ) c '
                   'JOIN cityprice c1 ON c.bpid = c1.bpid JOIN districttable d  ON c.BID = d.DTID '
                   'ORDER BY CTime DESC;')
            args = (start_date, end_date,
                    district_id, house_type)
            count, city_house_price = mysql_conn.fetchall(sql, args=args)
        finally:
            if "mysql_conn" in dir():  # 判断连接是否成功创建，创建了才能执行close()
                mysql_conn.close()

        return city_house_price

    def get_dtid_form_city_name(self, city_name: str) -> int:
        """
        从城市名称中获取区域id
        :param city_name: 城市名称
        :return: 区域id
        """
        mysql_conn = self.get_mysql_conn()
        district_id = None
        try:
            sql = ('SELECT DTID, `Name`, ParentID, JwParentID, siteType '
                   'FROM districttable '
                   'WHERE `Name` = %s '
                   'AND ParentID = 1 AND siteType = 1;')
            args = (city_name, )
            district_id = mysql_conn.fetchone(sql, args=args)
            if district_id:
                district_id = district_id.get("DTID")
            else:
                district_id = None
        finally:
            if "mysql_conn" in dir():  # 判断连接是否成功创建，创建了才能执行close()
                mysql_conn.close()

        return district_id


def main():
    house_price_dao = HousePriceDAO()

    # res = house_price_dao.get_city_house_price(
    #     year=2023, month=3,
    #     city_name="深圳", house_type="新房"
    # )
    # print(res)

    # count, res = house_price_dao.get_district_house_price(
    #     year=2023, month=3,
    #     city_name="深圳", house_type="新房"
    # )
    # print(count, res, sep='\n')

    # res = house_price_dao.get_past_year_new_house_price(city_name="深圳", house_type="新房")
    # print(res)

    # district_id = house_price_dao.get_dtid_form_city_name("深圳")
    # print(district_id)

    city_house_price = house_price_dao.get_city_house_price2(start_date="2023-01-01", end_date="2023-03-01", district_id=12, house_type=1)
    print(city_house_price)


if __name__ == '__main__':
    main()
